package com.zlht.dao.imp;

import com.zlht.dao.EmployeeDao;
import com.zlht.entity.Employee;
import com.zlht.entity.Store;
import com.zlht.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDaoImp implements EmployeeDao {
    @Override
    public List<Employee> list() {
        Connection connection = JDBCUtil.getConnection();
        String sql = "select e.id,e.name,e.gender,e.age,e.phone,e.store_id,s.name,e.position,e.username,e.password from employee e,store s where s.id = e.store_id;";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Employee> list = new ArrayList<>();
        try{
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            while (resultSet.next()){

                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String gender = resultSet.getString(3);
                String age = resultSet.getString(4);
                String phone = resultSet.getString(5);
                Integer store_id = resultSet.getInt(6);
                String store = resultSet.getString(7);
                String position = resultSet.getString(8);
                String username = resultSet.getString(9);
                String password = resultSet.getString(10);
                list.add(new Employee(id,name,gender,age,phone,store_id,store,position,username,password));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,resultSet);
        }
        return list;
    }

    @Override
    public List<Employee> search(String key, String value) {
        Connection connection = JDBCUtil.getConnection();
        String sql = "select e.id,e.name,e.gender,e.age,e.phone,e.store_id,s.name,e.position,e.username,e.password from employee e,store s where s.id = e.id and "+key+" like '%"+value+"%'";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Employee> list = new ArrayList<>();
        try{
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            while (resultSet.next()){

                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String gender = resultSet.getString(3);
                String age = resultSet.getString(4);
                String phone = resultSet.getString(5);
                Integer store_id = resultSet.getInt(6);
                String store = resultSet.getString(7);
                String position = resultSet.getString(8);
                String username = resultSet.getString(9);
                String password = resultSet.getString(10);
                list.add(new Employee(id,name,gender,age,phone,store_id,store,position,username,password));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,resultSet);
        }
        return list;
    }
    @Override
    public int save(Employee employee) {
        Connection connection = JDBCUtil.getConnection();
        String sql = "insert into employee (name,gender,age,phone,store_id,position,username,password) values (?,?,?,?,?,?,?,?)";
        PreparedStatement statement = null;
        Integer result = null;
        try{
            statement = connection.prepareStatement(sql);
            statement.setString(1,employee.getName());
            statement.setString(2,employee.getGender());
            statement.setString(3,employee.getAge());
            statement.setString(4,employee.getPhone());
            statement.setInt(5,employee.getStoreId());
            statement.setString(6,employee.getPosition());
            statement.setString(7,employee.getUsername());
            statement.setString(8,employee.getPassword());

            result = statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,null);
        }
        return result;
    }
    @Override
    public int update(Employee employee) {
        Connection connection = JDBCUtil.getConnection();
        String sql = "update employee set name = ?,gender = ?,age = ?,phone = ?,store_id = ?,position = ?,username = ?,password = ? where id =?";
        PreparedStatement statement = null;
        Integer result = null;
        try{
            statement = connection.prepareStatement(sql);
            statement.setString(1,employee.getName());
            statement.setString(2,employee.getGender());
            statement.setString(3,employee.getAge());
            statement.setString(4,employee.getPhone());
            statement.setInt(5,employee.getStoreId());
            statement.setString(6,employee.getPosition());
            statement.setString(7,employee.getUsername());
            statement.setString(8,employee.getPassword());
            statement.setInt(9,employee.getId());

            result =statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,null);
        }
        return result;
    }

    @Override
    public int delete(Integer id) {
        Connection connection = JDBCUtil.getConnection();
        String sql = "delete from employee where id = "+id;
        PreparedStatement statement = null;
        Integer result = null;
        try{
            statement = connection.prepareStatement(sql);

            result =statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,null);
        }
        return result;
    }

}
